/*
 Navicat Premium Dump SQL

 Source Server         : 12
 Source Server Type    : MySQL
 Source Server Version : 50744 (5.7.44-log)
 Source Host           : 43.143.250.149:33061
 Source Schema         : blog

 Target Server Type    : MySQL
 Target Server Version : 50744 (5.7.44-log)
 File Encoding         : 65001

 Date: 22/06/2025 17:48:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for categories
-- ----------------------------
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` text,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of categories
-- ----------------------------
BEGIN;
INSERT INTO `categories` (`category_id`, `name`, `description`) VALUES (1, '技术', '关于编程和技术的文章');
INSERT INTO `categories` (`category_id`, `name`, `description`) VALUES (2, '生活', '日常生活和感悟');
INSERT INTO `categories` (`category_id`, `name`, `description`) VALUES (3, '旅行', '旅行见闻和攻略');
INSERT INTO `categories` (`category_id`, `name`, `description`) VALUES (4, '工作', '工作');
COMMIT;

-- ----------------------------
-- Table structure for comments
-- ----------------------------
DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`post_id`) ON DELETE CASCADE,
  CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `comments_ibfk_3` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`comment_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of comments
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for post_categories
-- ----------------------------
DROP TABLE IF EXISTS `post_categories`;
CREATE TABLE `post_categories` (
  `post_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`post_id`,`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of post_categories
-- ----------------------------
BEGIN;
INSERT INTO `post_categories` (`post_id`, `category_id`) VALUES (2, 1);
INSERT INTO `post_categories` (`post_id`, `category_id`) VALUES (3, 1);
INSERT INTO `post_categories` (`post_id`, `category_id`) VALUES (1, 2);
INSERT INTO `post_categories` (`post_id`, `category_id`) VALUES (5, 2);
INSERT INTO `post_categories` (`post_id`, `category_id`) VALUES (4, 3);
COMMIT;

-- ----------------------------
-- Table structure for posts
-- ----------------------------
DROP TABLE IF EXISTS `posts`;
CREATE TABLE `posts` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `view_count` int(11) DEFAULT '0',
  `status` enum('published','draft') DEFAULT 'published',
  PRIMARY KEY (`post_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of posts
-- ----------------------------
BEGIN;
INSERT INTO `posts` (`post_id`, `user_id`, `title`, `content`, `created_at`, `updated_at`, `view_count`, `status`) VALUES (1, 1, '我的第一篇博客', '这是我的第一篇博客文章，欢迎阅读！', '2025-06-22 11:44:17', '2025-06-22 17:22:29', 8, 'published');
INSERT INTO `posts` (`post_id`, `user_id`, `title`, `content`, `created_at`, `updated_at`, `view_count`, `status`) VALUES (2, 1, 'PHP入门教程', '本文将介绍PHP的基础知识...', '2025-06-22 11:44:17', '2025-06-22 17:46:26', 1, 'published');
INSERT INTO `posts` (`post_id`, `user_id`, `title`, `content`, `created_at`, `updated_at`, `view_count`, `status`) VALUES (3, 1, '范德萨', '范德萨发时达范德萨', '2025-06-22 17:28:59', '2025-06-22 17:29:12', 1, 'published');
INSERT INTO `posts` (`post_id`, `user_id`, `title`, `content`, `created_at`, `updated_at`, `view_count`, `status`) VALUES (4, 1, '悉尼皇家复活节嘉年华', '超过一百九十年历史的悉尼皇家复活节嘉年华是由新南威尔士州的皇家农业\r\n协会所创办的。通过举行约12,500项农艺比赛和展示数量约高达14,000头的动物，该盛事一直致力于推广澳大利亚农业技术的卓越与创新。作为澳大利亚最大型的年度盛事，悉尼皇家复活节嘉年华将乡村搬到城市，呈现澳大利亚最美好的一面。从原始的传统到城市的活力，悉尼皇家复活节嘉年华(Sydney Royal Easter Show)弥漫着浓厚的澳大利亚气息。作为一个文化的标志，在城市中心的悉尼奥林匹克公园，每年都有来自六十多个国家，数以千计的国际游客参与这一盛事，一同分享澳大利亚的惬意生活嘉年华内容精彩丰富，从牛仔竞技大赛(International Rodeo)、国际伐木大赛(Woodchop)、马球比赛到嘉年华机动游戏、烟花表演、巡游汇演和现场表演，还有艺术品和手工艺精品，应有尽有。超过四百家的零售商铺，一百六十多家美食店铺让您可以尝遍最顶级的澳洲美味以及多种多样的国际美食。\r\n\r\n　　悉尼皇家复活节嘉年华是澳大利亚庆祝其文化和遗产的一项活动，它给各个年龄段、有着各种背景的人们留下了独特的记忆。', '2025-06-22 17:31:29', '2025-06-22 17:46:21', 7, 'published');
INSERT INTO `posts` (`post_id`, `user_id`, `title`, `content`, `created_at`, `updated_at`, `view_count`, `status`) VALUES (5, 1, '你好，这是一个文章', '你好，这是一个文章你好，这是一个文章你好，这是一个文章你好，这是一个文章你好，这是一个文章你好，这是一个文章你好，这是一个文章', '2025-06-22 17:46:47', '2025-06-22 17:46:54', 1, 'published');
COMMIT;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL,
  `display_name` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `avatar` varchar(255) DEFAULT 'default.jpg',
  `bio` text,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN;
INSERT INTO `users` (`user_id`, `username`, `password`, `email`, `display_name`, `created_at`, `avatar`, `bio`) VALUES (1, 'admin', '123456', 'admin@example.com', 'molin', '2025-06-22 11:44:17', 'default.jpg', 'molin博客');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
